package sg.edu.cs5226.workload;

import java.sql.Statement;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Random;

import sg.edu.cs5226.db.DBConnection;

public class DBWorkload {

	static int INTERVAL = 3000;

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	static String maxidQueryPerson = "select max(id) as value from person";
	static String maxpidQueryPersonDetail = "select max(pid) as value from person_detail";

	public DBWorkload() {

	}

	public void init() {
		try {
			Properties prop = new Properties();
			FileInputStream in = new FileInputStream("config.ini");
			prop.load(in);
			in.close();

			DBConnection db = new DBConnection(prop.getProperty("oracle.driver"));
			String url = prop.getProperty("oracle.serverurl");
			String user = prop.getProperty("oracle.username");
			String pass = prop.getProperty("oracle.password");
			conn = db.getConnection(url, user, pass);
			stmt = conn.createStatement();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public int getMaxId(String query) {
		try {
			rs = stmt.executeQuery(query);
			while(rs.next()) {
				return rs.getInt("value");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}

	public void run() {
		int maxIdPerson = getMaxId(maxidQueryPerson);
		int maxIdPersonDetail = getMaxId(maxpidQueryPersonDetail);
		int temp = 0, idt, pidt;
		Random ra = new Random();
		while(true) {
			try {
				int chance = ra.nextInt(4);
				switch(chance) {
				case 0: // select
					idt = maxIdPerson / 2;
					pidt = maxIdPersonDetail / 2;
					System.out.println("select * from person where id > " + idt + " order by name");
					System.out.println("select * from person_detail where pid > " + pidt + " order by id");
					stmt.executeQuery("select * from person where id > " + idt + " order by name");
					stmt.executeQuery("select * from person_detail where pid > " + pidt + " order by id");
					break;
				case 1: // insert
					maxIdPerson++;
					maxIdPersonDetail++;
					String gen = ((maxIdPerson % 2) == 0) ? "M" : "F";
					System.out.println("insert into person values (" + maxIdPerson + ", 'NAME " + maxIdPerson + "','" + gen + "')");
					System.out.println("insert into person_detail values (" + maxIdPersonDetail + ", 'Address " + maxIdPersonDetail + "', 'P" + maxIdPersonDetail + "'," + (int)(maxIdPerson / 2) + ")");
					stmt.executeQuery("insert into person values (" + maxIdPerson + ", 'NAMENAMENAMENAMENAMENAMENAME " + maxIdPerson + "','" + gen + "')");
					stmt.executeQuery("insert into person_detail values (" + maxIdPersonDetail + ", 'AddressAddressAddressAddressAddressAddressAddressAddressAddress " + maxIdPersonDetail + "', 'P" + maxIdPersonDetail + "'," + ra.nextInt(maxIdPerson) + ")");
					break;
				case 2: // delete
					idt = ra.nextInt(maxIdPerson);
					System.out.println("delete from person where id =" + idt);
					System.out.println("delete from person_detail where id =" + idt);
					stmt.executeQuery("delete from person where id =" + idt);
					stmt.executeQuery("delete from person_detail where id =" + idt);
					break;
				case 3: // join
					System.out.println("select * from person, person_detail where person.id = person_detail.id" + " order by pid");
					stmt.executeQuery("select * from person, person_detail where person.id = person_detail.id" + " order by pid");
					break;
				default:
					break;
				}
				temp++;
				if(temp == 2) {
					stmt.executeQuery("commit");
					stmt.executeQuery("analyze table person compute statistics");
					stmt.executeQuery("analyze table person_detail compute statistics");
					temp = 0;
				}
			
				Thread.sleep(INTERVAL);
			} catch(Exception e) {
				e.printStackTrace();
				break;
			}
		}
		System.out.println("Terminated!");
	}

	public void close() {
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		DBWorkload dbw = new DBWorkload();
		dbw.init();
		dbw.run();
	}
}
